from flask import Blueprint, render_template, request
import pymysql
import matplotlib
matplotlib.use('Agg')
import matplotlib.pyplot as plt
import io
import base64
from datetime import datetime

index_bp = Blueprint('index', __name__)

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'database': 'Ab_data',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}

def get_db_connection():
    return pymysql.connect(**DB_CONFIG)

def fetch_age_distribution():
    """获取鲍鱼年龄分布数据"""
    query = """
    SELECT rings AS age, COUNT(*) AS count
    FROM new_abalone
    GROUP BY rings
    ORDER BY rings
    """
    connection = get_db_connection()
    try:
        with connection.cursor() as cursor:
            cursor.execute(query)
            return cursor.fetchall()
    finally:
        connection.close()

def fetch_abalone_data(page=1, per_page=10):
    """获取鲍鱼数据（分页）"""
    offset = (page - 1) * per_page
    query = f"""
    SELECT id, sex, length, diameter, height, whole_weight, 
           shucked_weight, viscera_weight, shell_weight, rings,
           predicted_rings_LR, predicted_rings_DT_R, predicted_rings_RF_R
    FROM new_abalone
    ORDER BY id
    LIMIT {per_page} OFFSET {offset}
    """
    count_query = "SELECT COUNT(*) AS total FROM new_abalone"

    connection = get_db_connection()
    try:
        with connection.cursor() as cursor:
            cursor.execute(query)
            data = cursor.fetchall()
            cursor.execute(count_query)
            total = cursor.fetchone()['total']
            return data, total
    finally:
        connection.close()

def generate_age_chart(age_data):
    """生成年龄分布图表"""
    ages = [item['age'] for item in age_data]
    counts = [item['count'] for item in age_data]

    plt.figure(figsize=(10, 6))
    plt.bar(ages, counts, color='#1f77b4', alpha=0.7)
    plt.title('鲍鱼年龄分布', fontsize=14, fontweight='bold')
    plt.xlabel('年龄(环数)', fontsize=12)
    plt.ylabel('数量', fontsize=12)
    plt.grid(axis='y', linestyle='--', alpha=0.7)
    plt.tight_layout()

    img_buf = io.BytesIO()
    plt.savefig(img_buf, format='png')
    img_buf.seek(0)
    img_base64 = base64.b64encode(img_buf.read()).decode('utf-8')
    plt.close()

    return img_base64

def calculate_stats(abalone_data):
    """计算数据统计信息"""
    stats = {
        'total': len(abalone_data),
        'male': 0,
        'female': 0,
        'infant': 0,
        'avg_age': 0,
        'min_age': float('inf'),
        'max_age': 0
    }

    ages = []
    for item in abalone_data:
        if item['sex'] == 'M':
            stats['male'] += 1
        elif item['sex'] == 'F':
            stats['female'] += 1
        elif item['sex'] == 'I':
            stats['infant'] += 1

        age = item['rings']
        ages.append(age)
        if age < stats['min_age']:
            stats['min_age'] = age
        if age > stats['max_age']:
            stats['max_age'] = age

    if ages:
        stats['avg_age'] = round(sum(ages) / len(ages), 1)

    return stats

@index_bp.route('/')
def index():
    """首页路由"""
    age_data = fetch_age_distribution()
    chart_base64 = generate_age_chart(age_data)

    page = request.args.get('page', 1, type=int)
    per_page = 10

    abalone_data, total = fetch_abalone_data(page, per_page)
    stats = calculate_stats(abalone_data)

    total_pages = (total + per_page - 1) // per_page
    pages = list(range(max(1, page - 2), min(total_pages + 1, page + 3)))

    username = "yoo"

    return render_template('index.html',
                         username=username,
                         chart_base64=chart_base64,
                         abalone_data=abalone_data,
                         stats=stats,
                         page=page,
                         per_page=per_page,
                         total=total,
                         total_pages=total_pages,
                         pages=pages)